 -- EXEC sp_create_update_mark_and_pack_lot 'M52S32162ADM1','T700G.02W960917I07',10456, null, '09/17/2007', null, 'Mark & Pack','Mark &amp; Pack','UTAC',null,'Running','PROD','T700G.02', null, null

Alter PROC sp_create_update_mark_and_pack_lot 
( 
 @PART_NUMBER 	VARCHAR(50), 
 @LOT_NUMBER 	VARCHAR(50), 
 @QTY_IN 		INT,
 @QTY_OUT 		INT,
 @DATE_IN 		DATETIME,
 @DATE_OUT 		DATETIME,
 @INVENTORY_TYPE VARCHAR(50),
 @LOCATION_TYPE  VARCHAR(50),
 @VENDOR_NAME 	 VARCHAR(50),
 @expected_Date_Out VARCHAR(50),
 @LOT_STATUS 		VARCHAR(50),
 @lot_category		VARCHAR(50),
 @fab_lot_number	VARCHAR(50),
 @pkgType			VARCHAR(50),
 @stage_name		VARCHAR(50)
 )
 
 AS
 BEGIN
 
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	 	
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end 	
	
	if @fab_lot_number is null
	begin
		set @fab_lot_number = @LOT_NUMBER
	end
	 
	
 	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT, PROCESS_STEP WHERE PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT.PART_NUMBER LIKE @PART_NUMBER AND PRODUCT.PROCESSSTEP_ID = PROCESS_STEP.PROCESSSTEP_ID 
 	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = @LOCATION_TYPE AND L_VENDOR_NAME = @VENDOR_NAME
	
 	IF @PROCESS_STEP_ID IS NULL -- NEW PRODUCT OR PRODUCT NAME MISMATCH
 	BEGIN
 			SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER LIKE @PART_NUMBER AND PS_TYPE_NAME LIKE @INVENTORY_TYPE AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID
 			IF @PROCESS_STEP_ID IS NULL
			BEGIN
					--For Discrepancy Lot Notification Alert
					IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @lot_number AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @inventory_type AND IS_USER_NOTIFIED = 1 ) = 0 
					BEGIN
							INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Status, TimeStamp )
								VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @inventory_type,'Discrepancy Lot Notification', @Qty_In, 'Open', CURRENT_TIMESTAMP )
					END
											 			
 					-- DUMMY UPDATE TO AVOID SQLEXCEPTION
 					UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'
 					RETURN
			END
	END
 	
 	SELECT @LOT_ID = LOT_ID FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	
 	IF @LOT_ID IS NULL -- CHECK IF THE LOT EXISTS
	BEGIN -- CREATE LOT
		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, STAGE_NAME, TIMESTAMP,LOT_ENTERED_BY,LOT_CATEGORY, LOT_DROP_OUT) 
			VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @fab_lot_number, @LOT_NUMBER, @QTY_IN, @DATE_IN,@DATE_OUT, @LOT_STATUS, @stage_name, CURRENT_TIMESTAMP,1, @lot_Category, 0 )
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty,  Status, TimeStamp )
				VALUES ( @part_number, @lot_number, @fab_lot_number, @vendor_name, @inventory_type, 'New Lot Notification', @qty_in, 'Open', CURRENT_TIMESTAMP )
		--SELECT * FROM INVENTORY_LOT
		INSERT INTO INVENTORY_LOT (LOT_ID, IL_QTY_IN, IL_DATE_IN, EXPECTED_DATE_OUT, IL_PACKAGE_TYPE, TIMESTAMP) VALUES (@LOT_ID, @QTY_IN, @DATE_IN, @expected_Date_Out, @pkgType, CURRENT_TIMESTAMP )		
		
	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @LOT_STATUS, QTY_IN = @QTY_IN, LOT_CATEGORY = @lot_Category, LOT_DROP_OUT = 0 WHERE LOT_ID = @LOT_ID
	
		UPDATE INVENTORY_LOT SET IL_QTY_IN = @QTY_IN,EXPECTED_DATE_OUT =@expected_Date_Out, IL_PACKAGE_TYPE=@pkgType, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
	END

	/* Closing Transit Lots and Previous Process Lots*/
	Exec SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @lot_number, @fab_lot_number, @QTY_IN, @DATE_IN
   	
 
 END